package com.zr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.model.Course;
import com.zr.model.Mark;
import com.zr.model.Student;
import com.zr.model.Teacher;
import com.zr.model.User;
import com.zr.util.JDBCUtil;

public class StudentDao {
	/**
	 * 查询所有学生的成绩信息 分页显示
	 * @return 返回学生的集合
	 */
	public List<Mark> findAllStu(int start,int rows,int teacherId){
		List<Mark> mark=new ArrayList<Mark>();
		StringBuffer str=new StringBuffer("select teacher.tid,cname,sname,score,record.cid,mark.rid,student.sid ");
		str.append(" from course,student,mark,record,teacher ");
		str.append(" where student.sid=record.sid and course.cid=record.cid ");
		str.append(" and mark.rid=record.rid and course.tid=teacher.tid ");
		str.append(" and teacher.tid=? LIMIT ?,?");
		Connection con=JDBCUtil.getJDBConnection();
		try {
			PreparedStatement pst=con.prepareStatement(str.toString());
			pst.setInt(1, teacherId);
			pst.setInt(2, start);
			pst.setInt(3, rows);
			ResultSet rs=pst.executeQuery();
			while (rs.next()) {
				Mark m = new Mark();
				m.setTid(rs.getInt("tid"));
				m.setCname(rs.getString("cname"));
				m.setSname(rs.getString("sname"));
				m.setScore(rs.getInt("score"));
				m.setRid(rs.getInt("rid"));
				m.setSid(rs.getInt("sid"));
				m.setCid(rs.getInt("cid"));
				mark.add(m);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closedConnection(con);
		}
		return mark;
	}
	
/**
 * 查询学生的总数
 * @param teacherId 传入当前老师的id
 * @return 返回学生的总数
 */
	public int findAllStuCount(int teacherId){
		int scount=0;
		StringBuffer str=new StringBuffer("select count(student.sid) scount ");
		str.append(" from course,student,mark,record,teacher ");
		str.append(" where student.sid=record.sid and course.cid=record.cid ");
		str.append(" and mark.rid=record.rid and course.tid=teacher.tid ");
		str.append(" and teacher.tid=?");
		Connection con=JDBCUtil.getJDBConnection();
		try {
			PreparedStatement pst=con.prepareStatement(str.toString());
			pst.setInt(1, teacherId);
			ResultSet rs=pst.executeQuery();
			while (rs.next()) {
				scount=rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closedConnection(con);
		}
		return scount;
	}
	/**
	 * 查询当前老师下的被选的课程
	 * @param start
	 * @param rows
	 * @param teacherId
	 * @param cname
	 * @return
	 */
	public List<Mark> selectCname(int start,int rows,int teacherId,String cname){
		List<Mark> mark=new ArrayList<Mark>();
		StringBuffer str=new StringBuffer("select teacher.tid,cname,sname,score,record.cid,mark.rid,student.sid ");
		str.append(" from course,student,mark,record,teacher ");
		str.append(" where student.sid=record.sid and course.cid=record.cid ");
		str.append(" and mark.rid=record.rid and course.tid=teacher.tid ");
		str.append(" and teacher.tid=? and course.cname=? LIMIT ?,?");
		Connection con=JDBCUtil.getJDBConnection();
		try {
			PreparedStatement pst=con.prepareStatement(str.toString());
			pst.setInt(1, teacherId);
			pst.setString(2, cname);
			pst.setInt(3, start);
			pst.setInt(4, rows);
			ResultSet rs=pst.executeQuery();
			while (rs.next()) {
				Mark m = new Mark();
				m.setTid(rs.getInt("tid"));
				m.setCname(rs.getString("cname"));
				m.setSname(rs.getString("sname"));
				m.setScore(rs.getInt("score"));
				m.setRid(rs.getInt("rid"));
				m.setSid(rs.getInt("sid"));
				m.setCid(rs.getInt("cid"));
				mark.add(m);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closedConnection(con);
		}
		return mark;
	}
	/**
	 * 查询当前老师下被选课的课程的数量
	 * @param teacherId
	 * @return
	 */
	public int selectCnameCount(int teacherId,String cname){
		int scount=0;
		StringBuffer str=new StringBuffer("select count(student.sid) scount ");
		str.append(" from course,student,mark,record,teacher ");
		str.append(" where student.sid=record.sid and course.cid=record.cid ");
		str.append(" and mark.rid=record.rid and course.tid=teacher.tid ");
		str.append(" and teacher.tid=? and course.cname=?");
		Connection con=JDBCUtil.getJDBConnection();
		try {
			PreparedStatement pst=con.prepareStatement(str.toString());
			pst.setInt(1, teacherId);
			pst.setString(2, cname);
			ResultSet rs=pst.executeQuery();
			while (rs.next()) {
				scount=rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closedConnection(con);
		}
		return scount;
	}
}
